package com.lgr.store.utils;


import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import com.lgr.store.vo.MaterialReportVo;
import com.lgr.store.vo.OrderDetailVo;
import com.lgr.store.vo.OrdersVo;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import com.lgr.store.vo.MaterialInputVo;


//Excel导出
public class ExportToExcel {


	public String copyFile(String path,String descfile,int code){
		String srcfile = "";
		switch(code){

		case 1://批量入库数据导出表
			descfile= descfile +ContextUtils.getFilename()+".xls";//导出后的文件名称
			srcfile = path +"/Excel/materialOutput.xls";//模板文件
			break;
		case 2://仓库明细表
			descfile= descfile +ContextUtils.getFilename()+".xls";
			srcfile = path +"/Excel/flInput.xls";
			break;
		case 3:
			descfile= descfile +ContextUtils.getFilename()+".xls";
			srcfile = path +"/Excel/listStat.xls";
			break;

		}
		try {
			FileInputStream fis = new FileInputStream(srcfile);
			FileOutputStream fos = new FileOutputStream(descfile);
			byte [] buffer = new byte[1024*4];
			while(fis.read(buffer) != -1){
				fos.write(buffer);
			}
			fis.close();
			fos.close();
		} catch (Exception e) {

			e.printStackTrace();
		}
		return descfile;
		
	}
	HSSFCellStyle setStyle(HSSFWorkbook wb1,String format){
		HSSFCellStyle style = wb1.createCellStyle();
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		if(format !=null && !format.equals("")){
			style.setDataFormat(HSSFDataFormat.getBuiltinFormat(format));
		}
		return style;
	}

	
	//辅料数据导出
	public String fuLiaoOputput(List<MaterialInputVo> info,String path,String absPath,int code){
		POIFSFileSystem fs = null; 
		String filename="";
		int headRow = 2;
		
		try {
			filename=copyFile(path,absPath,code);
			fs = new POIFSFileSystem(new FileInputStream(filename));
			FileOutputStream fos = new FileOutputStream(filename);
			HSSFWorkbook wb1 = new HSSFWorkbook(fs); 
			HSSFSheet sheet = wb1.getSheetAt(0);

			
			HSSFCellStyle style1 = setStyle(wb1,"");
			HSSFCellStyle style3 = setStyle(wb1,"0.00");

			int size = info.size();
			int col=0;
			for(int i=0;i<size;i++){
				col=0;
				MaterialInputVo p = (MaterialInputVo)info.get(i);
				//创建行
				HSSFRow row = sheet.createRow(i+headRow);
				HSSFCell cell = null;
				//创建列
				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(i+1);//序号
				
				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(p.getSupplierName());//序号
				
				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				String s = p.getOprDate();
				if(s==null){
					s="";
				}else{
					s=s.substring(0,s.indexOf(" "));
				}
				cell.setCellValue(s); 
				
				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(p.getStorageName()); 

				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(p.getMaterialCode()); 
				
				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(p.getMaterialName()); 

				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(p.getStyle()); 

				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(p.getUnitName()); 
				
				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(p.getOrderId()); 
				
				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(p.getBatchNo());

				cell = row.createCell(col++);
				cell.setCellStyle(style3);
				cell.setCellValue(p.getOrderCnt());
				
				cell = row.createCell(col++);
				cell.setCellStyle(style3);
				cell.setCellValue(p.getOrderNo());
				
				cell = row.createCell(col++);
				cell.setCellStyle(style3);
				cell.setCellValue(p.getUserName());
			}
			wb1.write(fos);
			fos.close(); 
			wb1=null;
			fs=null;
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return filename;
	}
	public static String toDate(Date date){
		String str = "";
		if(date==null)
			str="";
		else
		{
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
			str= df.format(date);
		}
		return str;
	}
	
	
	
	//订单明细表
	public String flInput(List<OrdersVo> info, String path, String absPath, int code,String title){
		POIFSFileSystem fs = null;
		String filename="";
		int headRow = 2;

		try {
			filename=copyFile(path,absPath,code);
			fs = new POIFSFileSystem(new FileInputStream(filename));
			FileOutputStream fos = new FileOutputStream(filename);
			HSSFWorkbook wb1 = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb1.getSheetAt(0);
			//设置Excel标题
			sheet.getRow(0).getCell(0).setCellValue(title);
			int size = info.size();
			int col=0;
			HSSFCellStyle style1 = setStyle(wb1,"");;
			HSSFCellStyle style2 = setStyle(wb1,"0.00%");
			HSSFCellStyle style3 = setStyle(wb1,"0.000");
			int cnt=0;
			for(int i=0;i<size;i++){
				OrdersVo p = (OrdersVo)info.get(i);
				List<OrderDetailVo> dList = p.getDetailList();
				int len  = dList.size();
				for(int j=0;j<len;j++) {
					OrderDetailVo d =dList.get(j);
					col = 0;
					HSSFRow row = sheet.createRow(cnt + headRow);
					cnt++;
					HSSFCell cell = null;

					cell = row.createCell(col++);
					cell.setCellStyle(style1);
					cell.setCellValue(p.getOrderNo());//单据编号
					cell = row.createCell(col++);
					cell.setCellStyle(style1);
					String ss = p.getOrderDate();
					if (ss == null) {
						ss = "";
					} else {
						ss = ss.substring(0, ss.indexOf(" "));
					}
					cell.setCellValue(ss);//单据时间
					cell = row.createCell(col++);
					cell.setCellStyle(style1);
					cell.setCellValue(p.getEmpName());//开单人

					cell = row.createCell(col++);
					cell.setCellStyle(style1);
					String str="";
					if(p.getOrderType()==1){
						str="采购入库单";
					}else if(p.getOrderType()==2){
						str="采购退货单";
					}else if(p.getOrderType()==3){
						str="销售出库单";
					}else if(p.getOrderType()==4){
						str="销售退货单";
					}else if(p.getOrderType()==5){
						str="盘点损益单";
					}
					cell.setCellValue(str);//单据类型

					cell = row.createCell(col++);
					cell.setCellStyle(style1);
					cell.setCellValue(p.getSupplierName());//供应商

					cell = row.createCell(col++);
					cell.setCellStyle(style1);
					cell.setCellValue(d.getMaterialCode());//物料编码
					cell = row.createCell(col++);
					cell.setCellStyle(style1);
					cell.setCellValue(d.getMaterialName());//物料名称

					cell = row.createCell(col++);
					cell.setCellStyle(style1);
					cell.setCellValue(d.getBatchNo());

					cell = row.createCell(col++);
					cell.setCellStyle(style1);
					cell.setCellValue(d.getOrderId());

					cell = row.createCell(col++);
					cell.setCellStyle(style1);
					cell.setCellValue(d.getStorageName());

					cell = row.createCell(col++);
					cell.setCellStyle(style1);
					cell.setCellValue(d.getMaterialUnit());
					cell = row.createCell(col++);
					cell.setCellStyle(style3);
					cell.setCellValue(d.getOrderCnt());
				}
			}
			wb1.write(fos);
			fos.close();
			wb1=null;
			fs=null;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return filename;
	}
	//库存统计表
	public String listStat(List<MaterialReportVo> info, String path, String absPath, int code, String title){
		POIFSFileSystem fs = null;
		String filename="";
		int headRow = 2;

		try {
			filename=copyFile(path,absPath,code);
			fs = new POIFSFileSystem(new FileInputStream(filename));
			FileOutputStream fos = new FileOutputStream(filename);
			HSSFWorkbook wb1 = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb1.getSheetAt(0);
			//设置Excel标题
			sheet.getRow(0).getCell(0).setCellValue(title);
			int size = info.size();
			int col=0;
			HSSFCellStyle style1 = setStyle(wb1,"");;
			HSSFCellStyle style2 = setStyle(wb1,"0.00%");
			HSSFCellStyle style3 = setStyle(wb1,"0.000");
			for(int i=0;i<size;i++){
				MaterialReportVo p = (MaterialReportVo)info.get(i);
				col = 0;
				HSSFRow row = sheet.createRow(i + headRow);
				HSSFCell cell = null;
				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(p.getStorageName());

				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(p.getMaterialCode());

				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(p.getMaterialName());//物料名称

				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(p.getStyle());

				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(p.getBatchNo());

				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(p.getOrderId());

				cell = row.createCell(col++);
				cell.setCellStyle(style1);
				cell.setCellValue(p.getMaterialUnit());
				cell = row.createCell(col++);
				cell.setCellStyle(style3);
				cell.setCellValue(p.getOrderCnt());
			}
			wb1.write(fos);
			fos.close();
			wb1=null;
			fs=null;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return filename;
	}
}
